package com.chen.base.module.excel.utils;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.chen.base.module.excel.annotation.ExcelHandler;
import com.chen.base.module.excel.annotation.ExcelProper;
import com.chen.base.module.excel.listener.ExcelListener2;
import com.chen.base.module.excel.sheet.ExcelSheetData;
import com.chen.base.utils.AnnotationRouteUtil;
import com.chen.base.utils.CommonUtil;
import lombok.SneakyThrows;
import lombok.experimental.UtilityClass;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;

/**
 * HSSF: 特点 - 最多写入65536行数据，处理过程全部写入缓存，不操作磁盘。最后一次性写入磁盘，速度很快
 *           - 会发生内存溢出（OOM），超过65536行会报错
 * XSSF: 特点 - 可以写入大量数据，如20万条
 *           - 写入速度慢，非常耗内存，也会发生内存溢出
 * SXSSF: 特点 - 可以写入大量数据，速度一般，占用内存少
 *            - 过程中会产生临时文件，需要清理临时文件
 * 建议：数据量小使用HSSF，数据量大使用SXSSF
 * @Author: 陈锦龙
 * @Date: 2023/4/11 20:54
 */
@UtilityClass
public class ExcelUtil {

    /** 默认行高 */
    private final float defaultLineHeight = 30f;
    public final static ThreadLocal<Map<String, Object>> PARAM_MAP = new ThreadLocal<>();

    /**
     * EasyExcel 读取excel
     * @param filePath 文件路径
     * @param excelCode excel执行器code
     * @throws Exception Exception
     */
    public void read(String filePath, String excelCode) throws Exception {
        AnnotationRouteUtil.route(ExcelHandler.class, ExcelHandler::value, excelCode, (bean, method, handler) -> {
            ExcelReaderBuilder builder = EasyExcel.read(
                    filePath, handler.modelClass(), new ExcelListener2<>(bean, method, handler)
            );
            ExcelReaderSheetBuilder sheetBuilder;
            // 设置sheetName
            if (StrUtil.isNotBlank(handler.sheetName())){
                sheetBuilder = builder.sheet(handler.sheetName());
            }else {
                sheetBuilder = builder.sheet();
            }
            // 设置headRowNum
            if (handler.headRowNum() != 1){
                sheetBuilder.headRowNumber(handler.headRowNum());
            }
            // 读取
            sheetBuilder.doRead();
        });
    }

    /**
     * 导出excel数据
     * @param list 数据集
     * @param fileName 文件名
     * @param sheetName sheet名
     * @param clazz 实体
     */
    @SneakyThrows
    public <T> void writeExcel(List<T> list, String fileName, String sheetName, Class<T> clazz) {
        // 表头样式
        WriteCellStyle head = new WriteCellStyle();
        // 设置表头居中对齐
        head.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 内容样式
        WriteCellStyle cell = new WriteCellStyle();
        // 设置内容靠左对齐
        cell.setHorizontalAlignment(HorizontalAlignment.CENTER);

        EasyExcel.write(getOutputStream(fileName), clazz)
//                .withTemplate("") // 模版文件路径
                .excelType(ExcelTypeEnum.XLSX)
                .sheet(sheetName)
                .registerWriteHandler(new HorizontalCellStyleStrategy(head, cell))
                .doWrite(list);
    }

    private OutputStream getOutputStream(String fileName) throws Exception {
        HttpServletResponse response = CommonUtil.getResponse();

        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
        return response.getOutputStream();
    }

    /**
     * excel文件多个sheet导出
     * @param filePath 文件路径，如：C:\Users\NEWPC\Desktop\分摊面积表.xlsx
     * @param sheetDataList 对应多个sheet的数据集
     * @param includeColumnNames 自定义列名集
     * @return true: 写入成功
     */
    public <T> Boolean writeMoreSheet(String filePath, List<ExcelSheetData<T>> sheetDataList, Set<String> includeColumnNames){
        if (StrUtil.isBlank(filePath) || sheetDataList == null || sheetDataList.isEmpty()){
            return false;
        }
        ExcelWriter excelWriter = EasyExcel.write(filePath).build();
        int sheetCount = 0;
        for (ExcelSheetData<T> sheetData : sheetDataList) {
            List<T> datas = sheetData.getData();
            if (CollectionUtil.isEmpty(datas)){
                continue;
            }
            Class<?> dataClass = datas.get(0).getClass();
            String sheetName = sheetData.getSheetName();
            if (StrUtil.isBlank(sheetName)){
                sheetName = "sheet" + sheetCount;
            }

            WriteSheet writeSheet;
            ExcelWriterSheetBuilder builder = EasyExcel.writerSheet(sheetCount, sheetName).head(dataClass);
            if (CollectionUtil.isEmpty(includeColumnNames)){
                writeSheet = builder.build();
            }else {
                writeSheet = builder.includeColumnFiledNames(includeColumnNames).build();
            }

            excelWriter.write(datas, writeSheet);
            sheetCount += 1;
        }

        excelWriter.finish();
        return true;
    }

    /**
     * excel文件多个sheet导出
     * @param filePath 文件路径，如：C:\Users\NEWPC\Desktop\分摊面积表.xlsx
     * @param sheetDataList 对应多个sheet的数据集
     * @param tableTitle 表的标题集，默认合并前几行用于显示标题
     * @return true: 写入成功
     */
    public <T> Boolean writeMoreSheet(String filePath, List<ExcelSheetData<T>> sheetDataList, List<String> tableTitle){
        if (StrUtil.isBlank(filePath) || sheetDataList == null || sheetDataList.isEmpty()){
            return false;
        }
        if (tableTitle == null){
            tableTitle = Collections.emptyList();
        }

        Workbook workbook = null;
        try (FileOutputStream os = new FileOutputStream(filePath)){
            workbook = createWorkbook(sheetDataList);
            CellStyle cellStyle = getCellStyle(workbook);

            Field[] fields = null;
            for (int i = 0; i < sheetDataList.size(); i++) {
                if (fields == null){
                    fields = sheetDataList.get(0).getData().get(0).getClass().getDeclaredFields();
                }

                ExcelSheetData<T> sheetData = sheetDataList.get(i);
                // 创建并设置sheet的名称
                Sheet sheet = workbook.createSheet(sheetData.getSheetName() != null ? sheetData.getSheetName() : "sheet" + i);
                // 设置表标题相关
                setTableTitle(tableTitle, workbook, fields, sheet);
                // 设置表头相关
                setTableHeader(workbook, sheet, fields,tableTitle.size());

                // 设置sheet的行数据
                List<T> rowDataList = sheetData.getData();
                for (int j = 0; j < rowDataList.size(); j++) {
                    Row row = createRow(sheet,j + tableTitle.size() + 1);
                    T rowData = rowDataList.get(j);
                    // 设置每一行的数据
                    for (Field field : fields) {
                        ExcelProper header = field.getAnnotation(ExcelProper.class);
                        Cell cell = row.createCell(header.index());
                        // 设置单元格样式
                        cell.setCellStyle(cellStyle);
                        // 设置单元格内容
                        cell.setCellValue(field.get(rowData).toString());
                    }
                }
            }

            workbook.write(os);
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }finally {
            if (workbook != null && workbook instanceof SXSSFWorkbook){
                try {// 清除临时文件
                    ((SXSSFWorkbook)workbook).dispose();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }

        return true;
    }

    /**
     * 根据数据量的大小，创建何种类型的Workbook
     */
    private <T> Workbook createWorkbook(List<ExcelSheetData<T>> sheetDataList){
//        // 变更临时文件路径
//        TempFile.setTempFileCreationStrategy(new DefaultTempFileCreationStrategy(new File("E:\\temp")));
        int dataSize = 0;
        for (ExcelSheetData<T> sheetData : sheetDataList) {
            dataSize += sheetData.getData() != null ? sheetData.getData().size() : 0;
        }
        // SXSSF:当内存中的数据行量达到5000时，持久到文件中，避免OOM
        return dataSize <= 30000 ? new HSSFWorkbook() : new SXSSFWorkbook(5000);
    }

    /**
     * 设置表标题
     */
    private void setTableTitle(List<String> tableTitle, Workbook workbook, Field[] fields, Sheet sheet) {
        if (tableTitle.size() > 0){
            // 设置表标题的基本属性
            CellStyle titleCellStyle = workbook.createCellStyle();
            titleCellStyle.setFont(createFont(workbook, (short)25));
            setAlignment(titleCellStyle);

            for (int t = 0; t < tableTitle.size(); t++) {
                // 每个shell合并标题的单元格
                sheet.addMergedRegion(new CellRangeAddress(t,t,0,fields.length - 1));
                // 获取第一个单元格
                Cell mergedCell = sheet.createRow(t).createCell(0);
                mergedCell.setCellStyle(titleCellStyle);
                // 表标题赋值
                mergedCell.setCellValue(tableTitle.get(t));
            }
        }
    }

    /**
     * 设置表头相关
     */
    private void setTableHeader(Workbook workbook, Sheet sheet, Field[] fields,Integer titleSize) {
        List<ExcelProper> headerList = new ArrayList<>();
        for (Field field : fields) {
            field.setAccessible(true);
            headerList.add(field.getAnnotation(ExcelProper.class));
        }
        // 表头按照索引升序
        headerList = headerList.stream().sorted(Comparator.comparing(ExcelProper::index)).collect(Collectors.toList());

        Row headerRow = createRow(sheet,titleSize);
        CellStyle headCellStyle = getCellStyle(workbook);
        headCellStyle.setFont(createFont(workbook, null));
        for (int j = 0; j < headerList.size(); j++) {
            ExcelProper proper = headerList.get(j);
            // 设置列宽
            sheet.setColumnWidth(j,proper.columnWidth());
            Cell cell = headerRow.createCell(j);
            cell.setCellStyle(headCellStyle);
            cell.setCellValue(proper.value());
        }
    }

    private Row createRow(Sheet sheet,int index){
        Row row = sheet.createRow(index);
        row.setHeightInPoints(defaultLineHeight);
        return row;
    }

    /**
     * 设置单元格的基本属性
     */
    private CellStyle getCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        // 上边框
        style.setBorderTop(BorderStyle.THIN);
        // 下边框
        style.setBorderBottom(BorderStyle.THIN);
        // 左边框
        style.setBorderLeft(BorderStyle.THIN);
        // 右边框
        style.setBorderRight(BorderStyle.THIN);
        setAlignment(style);
        // 自动换行
        style.setWrapText(true);
        return style;
    }

    /**
     * 字体样式
     */
    private Font createFont(Workbook workbook, Short fontSize){
        Font font = workbook.createFont();
        // 开启粗体显示
        font.setBold(true);
        if (fontSize != null){
            // 字体大小
            font.setFontHeightInPoints(fontSize);
        }
        return font;
    }

    /**
     * 居中
     */
    private void setAlignment(CellStyle style){
        // 水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
    }
}
